package cs4280.dao;

import cs4280.bean.House;
import cs4280.bean.Movie;
import cs4280.bean.Screening;
import cs4280.jdbc.DB;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ScreeningDAO {

    public static enum RESULT {

        OK, DUPLICATE, FAIL
    };

    public ArrayList<Screening> getScreenings() {
        ArrayList<Screening> screenings = new ArrayList<Screening>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT [id], [movieid], [showtime], [houseid], [price], [seating] FROM [screening];");
            while (rs != null && rs.next() != false) {
                Screening screening = new Screening();
                screening.setId(rs.getInt(1));
                screening.setMovieid(rs.getInt(2));
                screening.setShowtime(rs.getString(3));
                screening.setHouseid(rs.getInt(4));
                screening.setPrice(rs.getInt(5));
                screening.setSeating(rs.getString(6));
                screenings.add(screening);
            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return screenings;
    }

    public ArrayList<Screening> getOneMovieScreenings(int id) {
        ArrayList<Screening> screenings = new ArrayList<Screening>();
        MovieDAO movieDAO = new MovieDAO();
        Movie movie = movieDAO.getMovie(id);

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT [id], [movieid], [showtime], [houseid], [price], [seating] FROM [screening] WHERE [movieid]=" + id);

            while (rs != null && rs.next() != false) {
                Screening screening = new Screening();
                screening.setId(rs.getInt(1));
                screening.setMovieid(rs.getInt(2));
                screening.setShowtime(rs.getString(3));
                screening.setHouseid(rs.getInt(4));
                screening.setPrice(rs.getInt(5));
                screening.setSeating(rs.getString(6));
                screening.setMoviename(movie.getName());
                screenings.add(screening);

            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return screenings;
    }

    public Screening getScreening(int id) {
        Screening screening = null;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [movieid], [showtime], [houseid], [price], [seating] FROM [screening] WHERE [id] = ?;");
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next() != false) {
                screening = new Screening();
                screening.setId(rs.getInt(1));
                screening.setMovieid(rs.getInt(2));
                screening.setShowtime(rs.getString(3));
                screening.setHouseid(rs.getInt(4));
                screening.setPrice(rs.getInt(5));
                screening.setSeating(rs.getString(6));
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return screening;
    }

    public RESULT setSeating(int id, String newS) {
        RESULT msg = RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("UPDATE [screening] SET seating=? WHERE id=" + id);
            pstmt.setString(1, newS);
            if (pstmt.executeUpdate() > 0) {
                msg = RESULT.OK;
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }
        return msg;
    }

    public RESULT updateScreening(Screening newScreening) {

        RESULT result = ScreeningDAO.RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [screening] WHERE [id] =?");
            pstmt.setInt(1, newScreening.getId());
            //boolean exist = pstmt.execute();
            ResultSet rs = pstmt.executeQuery();

            BookingDAO bookingDAO = new BookingDAO();
            
            //screening exists

            if (rs != null && rs.next() != false&& !bookingDAO.isBooked(newScreening) ) {


                pstmt = conn.prepareStatement("UPDATE [screening] SET [showtime]=?,[houseid]=?,[price]=? ,[seating] =? WHERE [id]=?");

               pstmt.setString(1, newScreening.getShowtime());
               pstmt.setInt(2,newScreening.getHouseid());
               pstmt.setInt(3,newScreening.getPrice());
               pstmt.setInt(5,newScreening.getId());


               House house= new House();
                HouseDAO houseDAO = new HouseDAO();
                house=houseDAO.getHouse(newScreening.getHouseid());

                int seatNum = house.getRownumber()*house.getColnumber();
                String[] seat=new  String[seatNum];
                for(int i=0;i<seatNum;i++){
                    seat[i]="0";
                }
                String seating="";
                for(int j=0;j<seatNum;j++){
                    seating+=seat[j];

                }
                pstmt.setString(4, seating);


                int rows = pstmt.executeUpdate();

                //successfully add the movie
                if (rows > 0) {
                    result = ScreeningDAO.RESULT.OK;
                } //insert movie fail
            } // return duplicate msg

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;

    }


    public RESULT addScreening(Movie newMovie,Screening newScreening) {

        RESULT result = RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [screening] WHERE [movieid] =? AND [showtime]=? AND[houseid]=?");
            pstmt.setInt(1, newMovie.getId());
            pstmt.setString(2,newScreening.getShowtime());
            pstmt.setInt(3,newScreening.getHouseid());

            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next() != false) {
                result = ScreeningDAO.RESULT.DUPLICATE;
            } // return duplicate msg
            else {

                House house= new House();
                HouseDAO houseDAO = new HouseDAO();
                house=houseDAO.getHouse(newScreening.getHouseid());

                int seatNum = house.getRownumber()*house.getColnumber();
                String[] seat=new  String[seatNum];
                for(int i=0;i<seatNum;i++){
                    seat[i]="0";
                }
                String seating="";
                for(int j=0;j<seatNum;j++){
                    seating+=seat[j];

                }
                
                PreparedStatement pstmt_screening = conn.prepareStatement("INSERT INTO [screening]([movieid],[showtime],[houseid],[price],[seating]) VALUES(?,?,?,?,?)");
                pstmt_screening.setInt(1, newMovie.getId());
                pstmt_screening.setString(2, newScreening.getShowtime());
                pstmt_screening.setInt(3,newScreening.getHouseid());
                pstmt_screening.setInt(4,newScreening.getPrice());
                pstmt_screening.setString(5,seating);

                int screen_update = pstmt_screening.executeUpdate();
                if(screen_update>0)
                    result = ScreeningDAO.RESULT.OK;
                 //insert movie fail
                else 
                    result = ScreeningDAO.RESULT.FAIL;
                

            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;

    }

    public RESULT deleteScreening(Screening newScreening) {

        RESULT result = RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [screening] WHERE [id] =?");
            pstmt.setInt(1, newScreening.getId());
            //boolean exist = pstmt.execute();
            ResultSet rs = pstmt.executeQuery();

            BookingDAO bookingdao = new BookingDAO();

            //screening exists

            if (rs != null && rs.next() != false &&!bookingdao.isBooked(newScreening)) {
                pstmt = conn.prepareStatement("DELETE FROM [screening] WHERE [id]=?");
                pstmt.setInt(1, newScreening.getId());

                int rows = pstmt.executeUpdate();
                pstmt = conn.prepareStatement("DELETE FROM [booking] WHERE [screeningid]=?");
                pstmt.setInt(1, newScreening.getId());

                int cols = pstmt.executeUpdate();


                //successfully delete the movie
                if (rows > 0) {
                    result = ScreeningDAO.RESULT.OK;
                } //insert movie fail


            }

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;

    }
}
